﻿using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using UnityEngine;
using System.Linq;
using System;
using Base.Data;
using Base.Custom;

class DBOTABELLIST
{
    public static string[] IDList = {
        "HVAC区",
        "安全设备",
        "部件",
        "常规模型",
        //"场地",
        "橱柜",
        "窗",
        "导线",
        "灯具",
        "地形",
        "电话设备",
        "电缆桥架",
        "电缆桥架配件",
        "电气设备",
        "电气装置",
        "风道末端",
        "风管",
        "风管附件",
        "风管管件",
        "管道",
        "管道附件",
        "管件",
        "护理呼叫设备",
        "火警设备",
        "机械设备",
        "家具",
        "结构钢筋",
        "结构钢筋网",
        "结构桁架",
        "结构基础",
        "结构加强板",
        "结构框架",
        "结构柱",
        "栏杆扶手",
        "楼板",
        "楼板边缘",
        "楼梯",
        "轮廓",
        "门",
        "幕墙嵌板",
        "幕墙竖梃",
        "喷头",
        "坡道",
        "墙",
        "墙饰条",
        "软风管",
        "软管",
        "数据设备",
        "体量",
        "天花板",
        "停车场",
        "通讯设备",
        "卫浴装置",
        "屋顶",
        "线管",
        "线管配件",
        "檐沟",
        "照明设备",
        "柱",
        "专用设备"
    };
}
public class DBOperation : MonoBehaviour {

    static DBOperation _DBOperation;
    public static DBOperation dbOperation
    {
        get
        {
            if (_DBOperation == null)
            {
                _DBOperation = new DBOperation();
            }
            return _DBOperation;
        }
        set
        {
            _DBOperation = value;
        }
    }
    /// <summary>
    /// 获取索引值对应图元的数据，默认索引项为ID
    /// </summary>
    /// <param name="id"> 索引值 </param>
    /// <param name="type"> 索引项 </param>
    /// <param name="isAgainst"> 是否反向选取(不等于索引值的项),默认false</param>
    /// <returns>该图元的每项属性</returns>
    public Property[] getElementByID(string id, string dboname, string type = "ID", bool isAgainst = false)
    {
        List<string> IDList = DBOTABELLIST.IDList.ToList();
        //List<string> attrList = new List<string>();
        List<Property> prop = new List<Property>();
        string needAgainst = isAgainst ? "!" : "";
        foreach (var item in IDList)
        {
            string sqlStr2 = "SELECT name from "+dboname+".dbo.syscolumns WHERE id = (SELECT id FROM "+ dboname + ".dbo.sysobjects WHERE name = '" + item + "')";
            DataSet tableDS = (DataSet)SqlHelper.ExecuteScalar(sqlStr2);
            string sqlsel = "SELECT count(*) FROM "+ dboname + ".dbo." + item + " WHERE " + type + " " + needAgainst + "=" + id;
            DataSet ds1 = (DataSet)SqlHelper.ExecuteScalar(sqlsel);
            int i = Convert.ToInt32(ds1.Tables[0].Rows[0][0]);
            if (i != 0)
            {
                foreach (DataRow Row in tableDS.Tables[0].Rows)
                {
                    foreach (DataColumn Col in tableDS.Tables[0].Columns)
                    {
                        string sqlStr = "SELECT " + Row[Col].ToString().Trim() + " FROM "+dboname+".dbo." + item + " WHERE " + type + " " + needAgainst + "=" + id;
                        DataSet tableDS2 = (DataSet)SqlHelper.ExecuteScalar(sqlStr);
                        if (tableDS2.Tables.Count != 0)
                        {
                            string str = Row[Col].ToString().Trim();
                            if (!str.Contains("magi")&&!str.Contains("MC"))
                            {
                                prop.Add(new Property(str, tableDS2.Tables[0].Rows[0][0].ToString().Trim()));
                            }
                        }
                    }
                }
            }
        }
        return prop.ToArray();
    }

    /// <summary>
    /// 获取数据库中对应表的对应列的数据,多条件筛选AND
    /// </summary>
    /// <param name="type">条件索引项(k)</param>
    /// <param name="value">条件索引值</param>
    /// <param name="sa">其余条件索引组</param>
    /// <param name="tableName">表名</param>
    /// <param name="attr">列名,默认为'ID'</param>
    /// <param name="needAgainst">索引(k)是否条件反向,默认为false</param>
    /// <param name="needDistinct">是否需要去重,默认false</param>
    /// <returns></returns>
    public List<string> getAttributeList(List<SqlAnd> sa, string attr = "ID", bool needDistinct = false)
    {
        SqlHelper.dboname = sa[0].value;
        List<string> IDList = new List<string>();
        string sqlStr = "SELECT " + attr + " FROM " + sa[0].value+".dbo.Fittings";
        if (sa.Count > 1)
            sqlStr += " WHERE " + sa[1].key + " = " + "'" + sa[1].value + "'";
        //SqlParameter sp = new SqlParameter("@value", sa[1].value);
        for (int i = 2; i < sa.Count; i++)
        {
            sqlStr += " AND " + sa[i].key + " = '" + sa[i].value + "' ";
        }

        print("拼接："+sqlStr);
        DataSet tableDS = (DataSet)SqlHelper.ExecuteScalar(sqlStr);
        foreach (DataRow Row in tableDS.Tables[0].Rows)
        {
            foreach (DataColumn Col in tableDS.Tables[0].Columns)
            {
                IDList.Add(Row[Col].ToString().Trim());
            }
        }
        if (needDistinct)
        {
            return IDList.Distinct().ToList();
        }
        return IDList;
    }




    /// <summary>
    /// 获取数据库中对应表的对应列的数据
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="attr">列名,默认为'ID'</param>
    /// <param name="needDistinct">是否需要去重,默认false</param>
    /// <returns></returns>
    //public List<string> getAttributeList(string tableName, string attr = "ID", bool needDistinct = false)
    //{
    //    List<string> IDList = new List<string>();
    //    string sqlStr = "SELECT " + attr + " FROM " + con.dataBase + ".dbo." + tableName;
    //    DataSet tableDS = (DataSet)SqlHelper.ExecuteScalar(sqlStr);

    //    foreach (DataRow Row in tableDS.Tables[0].Rows)
    //    {
    //        foreach (DataColumn Col in tableDS.Tables[0].Columns)
    //        {
    //            IDList.Add(Row[Col].ToString().Trim());
    //        }
    //    }
    //    if (needDistinct)
    //    {
    //        return IDList.Distinct().ToList();
    //    }
    //    return IDList;
    //}

    /// <summary>
    /// 获取数据库中对应表的对应列的数据（DataTable类型）
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="attr">列名,默认为'*',即整张表</param>
    /// <returns></returns>
    //public DataTable getTable(string tableName, string attr = "*")
    //{
    //    List<string> IDList = new List<string>();
    //    string sqlStr = "SELECT " + attr + " FROM " + con.dataBase + ".dbo." + tableName;
    //    DataSet tableDS = (DataSet)SqlHelper.ExecuteScalar(sqlStr);
    //    DataTable dt = tableDS.Tables[0];
    //    return dt;
    //}

    /// <summary>
    /// 获取数据库中对应表的对应列的数据,条件筛选AND
    /// </summary>
    /// <param name="type">条件索引项</param>
    /// <param name="value">条件索引值</param>
    /// <param name="tableName">表名</param>
    /// <param name="attr">列名,默认为'ID'</param>
    /// <param name="needAgainst">索引是否条件反向,默认为false</param>
    /// <param name="needDistinct">是否需要去重,默认false</param>
    /// <returns></returns>
    //public List<string> getAttributeList(string tableName, string type, string value, string attr = "ID", bool needAgainst = false, bool needDistinct = false)
    //{
    //    List<string> IDList = new List<string>();
    //    SqlParameter sp = new SqlParameter("@value", value);
    //    string against = needAgainst ? "!" : "";
    //    string sqlStr = "SELECT " + attr + " FROM " + tableName;
    //    sqlStr += " WHERE " + type + " " + against + "= @value";
    //    DataSet tableDS = (DataSet)SqlHelper.ExecuteScalar(sqlStr,sp);

    //    foreach (DataRow Row in tableDS.Tables[0].Rows)
    //    {
    //        foreach (DataColumn Col in tableDS.Tables[0].Columns)
    //        {
    //            IDList.Add(Row[Col].ToString().Trim());
    //        }
    //    }
    //    if (needDistinct)
    //    {
    //        return IDList.Distinct().ToList();
    //    }
    //    return IDList;
    //}
    /// <summary>
    /// 获取数据库中对应表的对应列的数据,多条件筛选AND
    /// </summary>
    /// <param name="type">条件索引项(k)</param>
    /// <param name="value">条件索引值</param>
    /// <param name="sa">其余条件索引组</param>
    /// <param name="tableName">表名</param>
    /// <param name="attr">列名,默认为'ID'</param>
    /// <param name="needAgainst">索引(k)是否条件反向,默认为false</param>
    /// <param name="needDistinct">是否需要去重,默认false</param>
    /// <returns></returns>
    //public List<string> getAttributeList(string tableName, string type, string value, List<sqlAND> sa, string attr = "ID",bool needAgainst = false, bool needDistinct = false)
    //{
    //    List<string> IDList = new List<string>();
    //    string against = needAgainst ? "!" : "";
    //    string sqlStr = "SELECT " + attr + " FROM " + tableName;
    //    sqlStr += " WHERE " + type + against + "= @value";
    //    SqlParameter sp = new SqlParameter("@value", value);
    //    foreach (var item in sa)
    //    {
    //        sqlStr += " AND " + item.type + "='" + item.value + "'";
    //    }
    //    DataSet tableDS = (DataSet)SqlHelper.ExecuteScalar(sqlStr,sp);
    //    foreach (DataRow Row in tableDS.Tables[0].Rows)
    //    {
    //        foreach (DataColumn Col in tableDS.Tables[0].Columns)
    //        {
    //            IDList.Add(Row[Col].ToString().Trim());
    //        }
    //    }
    //    if (needDistinct)
    //    {
    //        return IDList.Distinct().ToList();
    //    }
    //    return IDList;
    //}
    //public List<string> getAttributeList(string tableName, string type, string value, List<sqlAND> sa,int index, string attr = "ID", bool needAgainst = false, bool needDistinct = false)
    //{
    //    List<string> IDList = new List<string>();
    //    string against = needAgainst ? "!" : "";
    //    string sqlStr = "SELECT " + attr + " FROM " + tableName;
    //    sqlStr += " WHERE " + type + against + "= @value";
    //    SqlParameter sp = new SqlParameter("@value", value);
    //    for (int i = 0; i < index; i++)
    //    {
    //        sqlStr += " AND " + sa[i].type + "='" + sa[i].value + "'";
    //    }
    //    DataSet tableDS = (DataSet)SqlHelper.ExecuteScalar(sqlStr, sp);
    //    foreach (DataRow Row in tableDS.Tables[0].Rows)
    //    {
    //        foreach (DataColumn Col in tableDS.Tables[0].Columns)
    //        {
    //            IDList.Add(Row[Col].ToString().Trim());
    //        }
    //    }
    //    if (needDistinct)
    //    {
    //        return IDList.Distinct().ToList();
    //    }
    //    return IDList;
    //}


    /// <summary>
    /// 修改表中的单个值
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="type">列名</param>
    /// <param name="sa">条件</param>
    /// <param name="target">修改后的值</param>
    //public void changeValue(string tableName, string type, sqlAND sa,string target)
    //{
    //    string sqlStr = " UPDATE " + tableName;
    //    sqlStr += " SET " + type + " = @target WHERE " + sa.type + " = @value";
    //    SqlParameter[] sp = {
    //        new SqlParameter("@target", target),
    //        new SqlParameter("@value", sa.value)
    //    };
    //    SqlHelper.ExecuteNonQuery(sqlStr,sp);
    //}
    /// <summary>
    /// 删除某一行的值
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="sa">条件</param>
    //public void deleteValue(string tableName, sqlAND sa)
    //{

    //    string sqlStr = "DELETE FROM " + tableName;
    //    sqlStr += " WHERE " + sa.type + " = @value";
    //    SqlParameter sp = new SqlParameter("@value",sa.value);
    //    SqlHelper.ExecuteNonQuery(sqlStr,sp);
    //}
    /// <summary>
    /// 对应表添加记录(存在sql注入攻击隐患)
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="values">需要添加的一条记录（该条记录每一列的值）</param>
    //public void addRecord(string tableName,string[] values,int startLocation = 0)
    //{
    //    string sqlStr = "IF EXISTS(SELECT Name FROM jichang..SysObjects Where Name = '"+ tableName +"')";
    //    sqlStr += "INSERT INTO " + tableName + " VALUES(";
    //    for (int i = startLocation; i < values.Length; i++)
    //    {
    //        if (i == values.Length - 1)
    //        {
    //            sqlStr += " '" + values[i] + "')";
    //        }
    //        else
    //        {
    //            sqlStr += " '" + values[i] + "',";
    //        }
    //    }
    //    SqlHelper.ExecuteNonQuery(sqlStr);
    //}

    ///// <summary>
    ///// 获取索引值对应图元的数据，默认索引项为ID(多条件筛选AND)
    ///// </summary>
    ///// <param name="id"> 索引值 </param>
    ///// <param name="sa"> 条件组 </param>
    ///// <param name="type"> 索引项 </param>
    ///// <param name="isAgainst"> 是否反向选取(不等于索引值的项,第一个索引条件),默认false</param>
    ///// <returns>该图元的每项属性</returns>
    //public PropertyDate getElementByID(string id, List<sqlAND> sa, string type = "ID", bool isFix = false, bool isAgainst = false)
    //{
    //    List<string> IDList = UnitLists.IDList.ToList();
    //    PropertyDate pd = new PropertyDate();
    //    List<string> attrList = new List<string>();
    //    pd.pop = new Dictionary<string, string>();
    //    pd.mission = new List<MissionDate>();
    //    string needAgainst = isAgainst ? "!" : "";
    //    if (isFix)
    //    {
    //        string sqlStr = "SELECT * FROM " + con.dataBase + ".dbo.Maintain WHERE " + type + " " + needAgainst + "=" + "'" + id + "'";
    //        foreach (var item in sa)
    //        {
    //        sqlStr += " AND " + item.type + "=" + "'" + item.value + "'";
    //        }
    //        DataSet tableDS = (DataSet)SqlHelper.ExecuteScalar(sqlStr);

    //        foreach (DataRow Rows in tableDS.Tables[0].Rows)
    //        {
    //            int i = 0;
    //            MissionDate ms = new MissionDate();
    //            foreach (DataColumn Col in tableDS.Tables[0].Columns)
    //            {
    //                switch (i)
    //                {
    //                    case 0: { ms.TaskId = (int)(Rows[Col]); i++; }; break;
    //                    case 1: { ms.ItemId = Rows[Col].ToString().Trim(); i++; }; break;
    //                    case 2: { ms.TaskName = Rows[Col].ToString().Trim(); i++; }; break;
    //                    case 3: { ms.Create_by = Rows[Col].ToString().Trim(); i++; }; break;
    //                    case 4: { ms.Recived_by = Rows[Col].ToString().Trim(); i++; }; break;
    //                    case 5: { ms.StartTime = Rows[Col].ToString().Trim(); i++; }; break;
    //                    case 6: { ms.Describe = Rows[Col].ToString().Trim(); i++; }; break;
    //                    case 7: { ms.TaskTag = Rows[Col].ToString().Trim(); i++; }; break;
    //                    case 8: { ms.TaskState = Rows[Col].ToString().Trim(); i++; }; break;
    //                    case 9: { ms.TaskImage = Rows[Col].ToString().Trim(); i++; }; break;
    //                    case 10: { ms.Record = Rows[Col].ToString().Trim(); i++; }; break;
    //                    default:; break;
    //                }
    //            }
    //            pd.mission = new List<MissionDate>();
    //            pd.mission.Add(ms);
    //        }
    //    }
    //    else
    //    {
    //        foreach (var item in IDList)
    //        {
    //            string sqlStr2 = "SELECT name from " + con.dataBase + ".dbo.syscolumns WHERE id = (SELECT id FROM " + con.dataBase + ".dbo.sysobjects WHERE name = '" + item + "')";
    //            DataSet tableDS = (DataSet)SqlHelper.ExecuteScalar(sqlStr2);
    //            foreach (DataRow Row in tableDS.Tables[0].Rows)
    //            {
    //                foreach (DataColumn Col in tableDS.Tables[0].Columns)
    //                {
    //                    string sqlStr = "SELECT " + Row[Col].ToString().Trim() + " FROM " + con.dataBase + ".dbo." + item + " WHERE " + type + " " + needAgainst + "='" + id + "'";
    //                    foreach (var it in sa)
    //                    {
    //                        sqlStr += " AND " + it.type + "=" + "'" + it.value + "'";
    //                    }
    //                    DataSet tableDS2 = (DataSet)SqlHelper.ExecuteScalar(sqlStr);
    //                    if (tableDS.Tables.Count != 0)
    //                    {
    //                        pd.pop.Add(Row[Col].ToString().Trim(), tableDS2.Tables[0].Rows[0][0].ToString().Trim());
    //                    }
    //                }
    //            }
    //        }
    //    }
    //    return pd;
    //}
    ///// <summary>
    ///// 获取索引值对应图元的数据，默认索引项为ID(多条件组合OR)
    ///// </summary>
    ///// <param name="id"> 索引值 </param>
    ///// <param name="sa"> 条件组 </param>
    ///// <param name="type"> 索引项 </param>
    ///// <param name="isAgainst"> 是否反向选取(不等于索引值的项,第一个索引条件),默认false</param>
    ///// <returns>该图元的每项属性</returns>
    //public PropertyDate getElementByID(string id, sqlOR so, string type = "ID", bool isFix = false, bool isAgainst = false)
    //{
    //    List<string> IDList = UnitLists.IDList.ToList();
    //    PropertyDate pd = new PropertyDate();
    //    List<string> attrList = new List<string>();
    //    pd.pop = new Dictionary<string, string>();
    //    pd.mission = new List<MissionDate>();
    //    string needAgainst = isAgainst ? "!" : "";
    //    if (isFix)
    //    {
    //        string sqlStr = "SELECT * FROM " + con.dataBase + ".dbo.Maintain WHERE " + type + " " + needAgainst + "=" + "'" + id + "'";

    //        sqlStr += " OR " + so.type + " in (";
    //        foreach (var item in so.value)
    //        {
    //            if (item == so.value[so.value.Length - 1])
    //            {
    //                sqlStr += "'" + item + "')";
    //            }
    //            else
    //            {
    //                sqlStr += "'" + item + "',";
    //            }
    //        }
    //        DataSet tableDS = (DataSet)SqlHelper.ExecuteScalar(sqlStr);

    //        foreach (DataRow Rows in tableDS.Tables[0].Rows)
    //        {
    //            int i = 0;
    //            MissionDate ms = new MissionDate();
    //            foreach (DataColumn Col in tableDS.Tables[0].Columns)
    //            {
    //                switch (i)
    //                {
    //                    case 0: { ms.TaskId = (int)(Rows[Col]); i++; }; break;
    //                    case 1: { ms.ItemId = Rows[Col].ToString().Trim(); i++; }; break;
    //                    case 2: { ms.TaskName = Rows[Col].ToString().Trim(); i++; }; break;
    //                    case 3: { ms.Create_by = Rows[Col].ToString().Trim(); i++; }; break;
    //                    case 4: { ms.Recived_by = Rows[Col].ToString().Trim(); i++; }; break;
    //                    case 5: { ms.StartTime = Rows[Col].ToString().Trim(); i++; }; break;
    //                    case 6: { ms.Describe = Rows[Col].ToString().Trim(); i++; }; break;
    //                    case 7: { ms.TaskTag = Rows[Col].ToString().Trim(); i++; }; break;
    //                    case 8: { ms.TaskState = Rows[Col].ToString().Trim(); i++; }; break;
    //                    case 9: { ms.TaskImage = Rows[Col].ToString().Trim(); i++; }; break;
    //                    case 10: { ms.Record = Rows[Col].ToString().Trim(); i++; }; break;
    //                    default:; break;
    //                }
    //            }
    //            pd.mission = new List<MissionDate>();
    //            pd.mission.Add(ms);
    //        }
    //    }
    //    else
    //    {
    //        foreach (var item in IDList)
    //        {
    //            string sqlStr2 = "SELECT name from " + con.dataBase + ".dbo.syscolumns WHERE id = (SELECT id FROM " + con.dataBase + ".dbo.sysobjects WHERE name = '" + item + "')";
    //            DataSet tableDS = (DataSet)SqlHelper.ExecuteScalar(sqlStr2);
    //            foreach (DataRow Row in tableDS.Tables[0].Rows)
    //            {
    //                foreach (DataColumn Col in tableDS.Tables[0].Columns)
    //                {
    //                    string sqlStr = "SELECT " + Row[Col].ToString().Trim() + " FROM " + con.dataBase + ".dbo." + item + " WHERE " + type + " " + needAgainst + "='" + id + "'";
    //                    sqlStr += " OR " + so.type + " in (";
    //                    foreach (var ite in so.value)
    //                    {
    //                        if (ite == so.value[so.value.Length - 1])
    //                        {
    //                            sqlStr += "'" + ite + "')";
    //                        }
    //                        else
    //                        {
    //                            sqlStr += "'" + ite + "',";
    //                        }
    //                    }
    //                    DataSet tableDS2 = (DataSet)SqlHelper.ExecuteScalar(sqlStr);
    //                    if (tableDS.Tables.Count != 0)
    //                    {
    //                        pd.pop.Add(Row[Col].ToString().Trim(), tableDS2.Tables[0].Rows[0][0].ToString().Trim());
    //                    }
    //                }
    //            }
    //        }
    //    }
    //    return pd;
    //}
    //public List<string> getlevels(string tableName, sqlAND sa, string type = "图元ID")
    //{
    //    List<string> outer = new List<string>();
    //    SqlParameter sp = new SqlParameter("@value", sa.value);
    //    //string sqlstr = "SELECT DISTINCT "+ type +" from " + tableName + " WHERE @type <= @value ";
    //    string sqlstr = "SELECT DISTINCT 图元ID from Levels WHERE " + sa.type + @" <= @value AND 图元表名 != '线管' 
    //                        AND 图元表名 != '电缆桥架' 
    //                        AND 图元表名 != '风管' 
    //                        AND 图元表名 != '管道'
    //                        AND 图元表名 != '机械设备' ";
    //    DataSet ds = (DataSet)SqlHelper.ExecuteScalar(sqlstr,sp);
    //    foreach (DataRow row in ds.Tables[0].Rows)
    //    {
    //        outer.Add(row[0].ToString().Trim());
    //    }
    //    return outer;
    //}

    ///// <summary>
    ///// 获取开关所在的干路ID
    ///// </summary>
    ///// <param name="ID"></param>
    ///// <param name="strs"></param>
    ///// <returns></returns>
    //public bool getRoadIDList(string ID, out List<string> strs)
    //{
    //    strs = new List<string>();
    //    string sqlstr = "SELECT 路径ID列表 FROM Relations WHERE 路径ID列表 like '%" + ID + "%'";
    //    DataSet ds = (DataSet)SqlHelper.ExecuteScalar(sqlstr);
    //    foreach (DataRow row in ds.Tables[0].Rows)
    //    {
    //        strs.Add(row[0].ToString().Trim());
    //    }
    //    if (strs.Count != 0)
    //    {
    //        return true;
    //    }
    //    else
    //    {
    //        return false;
    //    }
    //}

}
